﻿using Dapper;
using DapperExtensions;
using DapperExtensions.Sql;
using SXFramWork.Core;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace SXFramWork.Core
{
    public class Sql<TEntity> : ISql<TEntity> where TEntity : class, new()
    {
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="dbContext"></param>
        public Sql(string connStr,IDbContext dbContext)
        {
            this.ConnStr = connStr;
            this.DbContext = dbContext;
        }
        public string ConnStr { get; set; }

        public IDbContext DbContext { get; set; }

        /// <summary>
        /// 【dapper】批量插入
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="list"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<int> AddDataListAsync(string sql, object list, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return await DbContext.NewConnection(tran).ExecuteAsync(sql, list, tran);
        }
        /// <summary>
        ///【dapper】***In*** 执行 新增，修改，删除 
        /// update TableName set EventId=@EventId where Id in @ids
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<int> ExecuteaInAync(string sql, object param, IUnitOfWork  unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return await DbContext.NewConnection(tran).ExecuteAsync(sql, param, tran);
        }
        /// <summary>
        /// 【dapper】执行 新增，修改，删除 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<int> ExecuteAsync(string sql, object param = null, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return await DbContext.NewConnection(tran).ExecuteAsync(sql, param, tran);
        }
        /// <summary>
        /// 执行sql 返回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<object> ExecuteScalarAsync(string sql, object param = null, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return await DbContext.NewConnection(tran).ExecuteScalarAsync(sql, param, tran);
        }
        /// <summary>
        /// 【dapper】查询一个对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<TEntity> GetDataAsync(string sql, object param = null, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return await DbContext.NewConnection(tran).QueryFirstOrDefaultAsync<TEntity>(sql, param, tran);
        }
        /// <summary>
        /// 查询集合【dapper】
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<List<TEntity>> GetDataListAsync(string sql, object param = null, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return await DbContext.NewConnection(tran).QueryAsync<TEntity>(sql, param, tran) as List<TEntity>;
        }
        /// <summary>
        /// 查询集合【dapper】
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<List<TResult>> GetDataListAsync<TResult>(string sql, object param = null, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return await DbContext.NewConnection(tran).QueryAsync<TResult>(sql, param, tran) as List<TResult>;
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="model"></param>
        /// <param name="check"></param>
        /// <param name="where"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<List<TEntity>> GetPageListAsync(string sql,QueryModel model, Dictionary<string, object> check = null, string where = "", object param = null,string orderBy="", IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            //分页信息
            var page = model;
            sql = sql.TrimStart();
            List<string> sqllist = new List<string>();

            //sql语句格式处理
            sql = sql.Trim().Replace("\n", "").Replace("\r", "");
            sql = Regex.Replace(sql, "\\s{2,}", " ");//去掉空格

            //总数
            sqllist.Add(GetToTalCountSql(sql, param, check, where) + ";");

            //处理CTE语句
            if (sql.ToLower().Trim().StartsWith(";with") || sql.ToLower().Trim().StartsWith("with"))
            {
                var arr = sql.Split(") select");
                sqllist.Add(arr[0]+")");
                sqllist.Add("select top " + page.PageSize + " * from( select row_number() over(order by ");
                sqllist.Add(page.SortBy + " " + page.SortType + ") as rownumber,* from(");
                sqllist.Add("select" + arr[1]);
            }
            else
            {
              
                sqllist.Add("select top " + page.PageSize + " * from( select row_number() over(order by ");
                sqllist.Add(page.SortBy + " " + page.SortType + ") as rownumber,* from(");
                sqllist.Add(sql);
            }
            //模糊查询     
            if (check != null && check.Count > 0)
            {
                foreach (var key in check.Keys)
                {
                    //筛选名称         
                    where = where + "  and " + key + " like '%" + check[key] + "%'";
                } 
            }
            //where 条件
            if (where.Trim().Length > 0)
            {
                sqllist.Add(where);
            }
            sqllist.Add(")as tb)as temp_tb where rownumber>(" + (page.CurrentPage - 1) * page.PageSize+")" );
            if (orderBy.NotNull())
                sqllist.Add(" order by "+orderBy);
            using (var reader = DbContext.NewConnection(tran).QueryMultiple(string.Concat(sqllist), param,tran))
            {
                model.TotalCount = await reader.ReadFirstAsync<int>();
                return await reader.ReadAsync<TEntity>() as List<TEntity>;
            }
            
        }
        /// <summary>
        /// 获得计算总数sql
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        private static string GetToTalCountSql(string sql, object param = null, Dictionary<string, object> check = null, string where = "")
        {
            var sql_end = new StringBuilder();
       
            sql = sql.ToLower().TrimStart();
            //处理CTE语句
            if (sql.ToLower().Trim(). StartsWith(";with")||sql.ToLower().Trim(). StartsWith("with"))
            {
                sql = sql.Trim().Replace("\n", "").Replace("\r", "");
                sql = Regex.Replace(sql, "\\s{2,}", " ");//去掉空格
                var arr = sql.Split(") select");
                sql_end.Append(arr[0]).Append(")");
                sql_end.Append("select count(1) totalcount from (");
                sql_end.Append("select"+arr[1]);
            }
            else
            {
                sql_end.Append("select  count(1) totalcount from ( ");
                sql_end.Append(sql);
            }
            //模糊查询     
            if (check != null && check.Count > 0)
            {
                foreach (var key in check.Keys)
                {
                    //筛选名称         
                    where = where + "  and " + key + " like '%" + check[key] + "%'";
                }
            }
            //where 条件
            if (where.Trim().Length > 0)
            {
                sql_end.Append(where);
            }
            sql_end.Append(")tb");
            return sql_end.To_String();
        }
        /// <summary>
        /// 【dapper】同时查询多张表数据（高级查询）
        /// "select *from A;select *from B";【dapper】
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return await DbContext.NewConnection(tran).QueryMultipleAsync(sql, param, tran);
        }
        /// <summary>
        /// 批量插入【高效率】
        /// </summary>
        /// <param name="entityList"></param>
        /// <param name="tablename"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task AddDataListAsync(List<TEntity> entityList, string tablename = "", IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tblName = tablename;
            var tran =unitOfWork?.Transaction;
            IDbConnection Db = DbContext.NewConnection(tran);
            using (var bulkCopy = new SqlBulkCopy(Db as SqlConnection, SqlBulkCopyOptions.TableLock, tran as SqlTransaction))
            {
                bulkCopy.BatchSize = entityList.Count();
                bulkCopy.DestinationTableName = tblName;
                var table = new DataTable();
                DapperExtensions.Sql.ISqlGenerator sqlGenerator = new SqlGeneratorImpl(new DapperExtensionsConfiguration());
                var classMap = sqlGenerator.Configuration.GetMap<TEntity>();
                var props = classMap.Properties.Where(x => x.Ignored == false).ToArray();
                foreach (var propertyInfo in props)
                {
                    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyInfo.PropertyType) ?? propertyInfo.PropertyInfo.PropertyType);
                }
                var values = new object[props.Count()];
                foreach (var itemm in entityList)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].PropertyInfo.GetValue(itemm, null);
                    }
                    table.Rows.Add(values);
                }
                await bulkCopy.WriteToServerAsync(table);
            }
        }
        /// <summary>
        /// 【dapper】查询并返回string
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public async Task<string> GetStrAsync(string sql,object param=null,IUnitOfWork unitOfWork=null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;

            if (!sql.Contains("top") && Regex.Matches(sql, @"select").Count == 1)
            {
                sql = sql.Replace("select", "select top 1 ");
            }
            object res = null;
            var tran = unitOfWork?.Transaction;
            res = await DbContext.NewConnection(tran).ExecuteScalarAsync(sql, param, tran);
            return res.To_String();
        }
    }
}
